package ro.gateway.aida.obj.db;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.sql.Types;

import ro.gateway.aida.db.DBPersistenceManager;
import ro.gateway.aida.db.PersistenceToken;
import ro.gateway.aida.obj.AIDAActivityObject;
import ro.gateway.aida.servlet.Constants;
import ro.gateway.aida.srv.IIDGenerator;

/**
 * <p>Title: Romanian AIDA</p>
 * <p>Description: :D application</p>
 * <p>Copyright: Copyright (comparator) 2003</p>
 * <p>Company: Romania Development Gateway </p>
 * @author Mihai Popoaei, mihai_popoaei@yahoo.com, smike@intellisource.ro
 * @author Mihai Postelnicu, mihai@ro-gateway.org
 * @version 1.0-* @version $Id: AIDAActivityObjectDB.java,v 1.1 2004/10/24 23:37:11 mihaipostelnicu Exp $
 */
public class AIDAActivityObjectDB extends DBPersistenceManager {
	private AIDAActivityObjectDB(PersistenceToken token) {
		super(token);
	}

	public static AIDAActivityObjectDB getManager(PersistenceToken token) {
		return new AIDAActivityObjectDB(token);
	}

	public long getLastId()
		throws SQLException {
		long result = 0;
		Connection con = getConnection();

		try {
			PreparedStatement ps = con.prepareStatement(GET_MAX_ID);
			ResultSet rs = ps.executeQuery();
			if (rs.next()) {
				result = rs.getLong(1);
			}
			rs.close();
			ps.close();
		} catch (SQLException sqlEx) {
			throw sqlEx;
		} finally {
			con.close();
		}

		return result;
	}

	public void insert(
		IIDGenerator idGenerators,
		AIDAActivityObject item)
		throws SQLException {

          Connection con = getConnection();
          if (item.getId() == -1) {
               item.setId(idGenerators.nextVal(Constants.IDGEN_OBJECTS));
          }

		try {
               PreparedStatement ps = con.prepareStatement(INSERT);

               ps.setLong(1, item.getId());
               ps.setLong(2, item.getActivityId());
               ps.setByte(3, item.getObjectType());
               long origin = item.getOrigin();
               if (origin == -1) {
                    ps.setNull(4, Types.INTEGER);
               } else {
                    ps.setLong(4, item.getOrigin());
               }
               ps.setString(5, item.getOriginDbKey());
               ps.setDate(6, new Date(item.getDate_modif()));
               ps.setDate(7, new Date(item.getHarvest_date()));
               if (item.getUser_id() == -1) {
                    ps.setNull(8, Types.INTEGER);
               } else {
                    ps.setLong(8, item.getUser_id());
               }
               ps.executeUpdate();

               ps.close();
		} catch (SQLException sqlEx) {
			throw sqlEx;
		} finally {
			con.close();
		}
	}

	public void delete( long id)
		throws SQLException {
		Connection con = getConnection();

		try {
			PreparedStatement ps = con.prepareStatement(DELETE);
			ps.setLong(1, id);
			ps.executeUpdate();
			ps.close();
		} catch (SQLException sqlEx) {
			throw sqlEx;
		} finally {
			con.close();
		}
	}

	public void delete(Connection con, long id) throws SQLException {

		try {
			PreparedStatement ps = con.prepareStatement(DELETE);
			ps.setLong(1, id);
			ps.executeUpdate();
			ps.close();
		} catch (SQLException sqlEx) {
			throw sqlEx;
		}
	}

	public void deleteForActivityByType(
		Connection con,
		long activity_id,
		byte object_type)
		throws SQLException {

		try {
			PreparedStatement ps = con.prepareStatement(DELETE_FOR_ACT_BY_TYPE);
			ps.setLong(1, activity_id);
			ps.setByte(2, object_type);
			ps.executeUpdate();
			ps.close();
		} catch (SQLException sqlEx) {
			throw sqlEx;
		} finally {
		}
	}

	public void deleteForActivityByType(
		long activity_id,
		byte object_type)
		throws SQLException {
		Connection con =getConnection();

		try {
			deleteForActivityByType(con, activity_id, object_type);
		} catch (SQLException sqlex) {
			throw sqlex;
		} finally {
			con.close();
		}
	}

	public void update(
		
		AIDAActivityObject item)
		throws SQLException {

		if (item.getId() == -1) {
			System.err.println("Incercare de update object fara id");
			return;
		}
		Connection con = getConnection();

		try {
			PreparedStatement ps = con.prepareStatement(UPDATE);

			ps.setLong(1, item.getActivityId());
			ps.setByte(2, item.getObjectType());
			long origin_db = item.getOrigin();
			if (origin_db == -1) {
				ps.setNull(3, Types.INTEGER);
			} else {
				ps.setLong(3, item.getOrigin());
			}
			ps.setString(4, item.getOriginDbKey());
			ps.setDate(5, new Date(item.getDate_modif()));
			ps.setDate(6, new Date(item.getHarvest_date()));
			if (item.getUser_id() == -1) {
				ps.setNull(7, Types.INTEGER);
			} else {
				ps.setLong(7, item.getUser_id());
			}
			ps.setLong(8, item.getId());
			ps.executeUpdate();

			ps.close();
		} catch (SQLException sqlEx) {
			throw sqlEx;
		} finally {
			con.close();
		}

		return;
	}

	public AIDAActivityObject getById(
		long id)
		throws SQLException {

		Connection con = getConnection();
		AIDAActivityObject result = null;

		try {
			PreparedStatement ps = con.prepareStatement(GET_BY_ID);
			ps.setLong(1, id);
			ResultSet rs = ps.executeQuery();
			if (rs.next()) {
				result = new AIDAActivityObject();
				result.setId(id);
				result.setActivityId(rs.getLong(1));
				if (rs.wasNull()) {
					result.setActivityId(-1);
				}
				result.setObjectType(rs.getByte(2));
				result.setOrigin(rs.getLong(3));
				if (rs.wasNull()) {
					result.setOrigin(-1);
				}
				result.setOriginDbKey(rs.getString(4));
				Timestamp time = rs.getTimestamp(5);
				result.setDate_modif(time != null ? time.getTime() : 0);
				time = rs.getTimestamp(6);
				result.setHarvest_date(time != null ? time.getTime() : 0);
				result.setUser_id(rs.getLong(7));
				if (rs.wasNull()) {
					result.setUser_id(-1);
				}
			}
			rs.close();
			ps.close();
		} catch (SQLException sqlEx) {
			throw sqlEx;
		} finally {
			con.close();
		}

		return result;
	}

	/**
	 * ???
	 * @deprecated
	 * @param result
	 * @throws SQLException
	 */
	private void getById(		
		AIDAActivityObject result)
		throws SQLException {

		if (result == null) {
			System.err.println("getById (aida object) -> para null");
			return;
		}

		Connection con = getConnection();

		try {
			PreparedStatement ps = con.prepareStatement(GET_BY_ID);
			ps.setLong(1, result.getId());
			ResultSet rs = ps.executeQuery();
			if (rs.next()) {
				result = new AIDAActivityObject();
				result.setActivityId(rs.getLong(1));
				result.setObjectType(rs.getByte(2));
				result.setOrigin(rs.getLong(3));
				result.setOriginDbKey(rs.getString(4));
				result.setDate_modif(rs.getTimestamp(5).getTime());
				result.setHarvest_date(rs.getTimestamp(6).getTime());
				result.setUser_id(rs.getLong(7));
			}
			rs.close();
			ps.close();
		} catch (SQLException sqlEx) {
			throw sqlEx;
		} finally {
			con.close();
		}
	}

	private static final String GET_MAX_ID = "SELECT max(id) FROM objects";

	private static final String INSERT =
		"INSERT INTO objects (id, activity_id, obj_type, origin, "
			+ "origin_dbk, date_modif, harvest_date, user_id) "
			+ "VALUES (?,?,?,?,?,?,?,?)";

	private static final String UPDATE =
		"UPDATE objects SET activity_id = ?, obj_type = ?, origin = ?, "
			+ "origin_dbk = ?, date_modif = ?, harvest_date = ?, user_id = ? "
			+ "WHERE id = ?";
	private static final String GET_BY_ID =
		"SELECT activity_id, obj_type, origin, origin_dbk, date_modif, harvest_date, user_id "
			+ "FROM objects "
			+ "WHERE id = ?";

	private static final String DELETE = "DELETE FROM objects WHERE id=?";

	private static final String DELETE_FOR_ACT_BY_TYPE =
		"DELETE FROM objects WHERE activity_id=? AND obj_type=?";
}